﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Threading;
using LiveResults.OnlineLib;
using System.Data.H2;

namespace LiveResults
{
    public partial class FrmNewProject : Form
    {
        public FrmNewProject()
        {
            InitializeComponent();
            cmbOnlineEvent.DataSource = new string[] { "Downloading events from online-server.. Please wait" };
            cmbOlaType.DataSource = new[] { new OlaParser.OlaListItem(OlaParser.OLA_CONNECTION_TYPE.OLA5_INTERNAL_DB), new OlaParser.OlaListItem(OlaParser.OLA_CONNECTION_TYPE.OLA5_SQLSERVER) };
            wizard1.NextEnabled = false;
        }

        private void FrmNewProject_Shown(object sender, EventArgs e)
        {
            ThreadPool.QueueUserWorkItem(new WaitCallback(delegate
            {
                List<Competition> comps = LiveServer.GetAvailableCompeitiotns();
                var list = (from c in comps
                            orderby c.Date descending, c.Name, c.Organizer
                            select c).ToList();
                cmbOnlineEvent.Invoke(new MethodInvoker(delegate
                    {
                        cmbOnlineEvent.DataSource = list;
                    }));
            }));
        }

        private void wizardPage1_CloseFromNext(object sender, Gui.Wizard.PageEventArgs e)
        {
            lblValidating.Text = "Validating password, please wait";
            Application.DoEvents();
            string msg;
            if (LiveServer.ValidatePassword((cmbOnlineEvent.SelectedItem as Competition).Id, txtOnlinePassword.Text, out msg))
            {
            }
            else
            {
                lblValidating.Text = msg;
                e.Page = wizardPage1;
            }
        }

        private void btnOpen_Click(object sender, EventArgs e)
        {
            FolderBrowserDialog diag = new FolderBrowserDialog();
            diag.SelectedPath = txtOEDir.Text;
            diag.Description = "Select path that OLEinzel export files to";
            diag.RootFolder = Environment.SpecialFolder.MyComputer;
            if (diag.ShowDialog(this) == System.Windows.Forms.DialogResult.OK)
            {
                txtOEDir.Text = diag.SelectedPath;
            }
        }

        private void wizardPage4_ShowFromNext(object sender, EventArgs e)
        {
        }

        private void wizardPage2_CloseFromNext(object sender, Gui.Wizard.PageEventArgs e)
        {
            if (rdoOE2003.Checked || rdoOE2010.Checked)
            {
                e.Page = pageOE;
            }
        }

        private void pageOE_CloseFromNext(object sender, Gui.Wizard.PageEventArgs e)
        {
            BroadCastProject pr = new BroadCastProject();
            Competition c = cmbOnlineEvent.SelectedItem as Competition;
            pr.EventDate = c.Date;
            pr.EventID = c.Id;
            pr.EventName = c.Name;
            pr.Password = txtOnlinePassword.Text;
            EventSoftwareOE oe = new EventSoftwareOE();
            if (rdoOE2003.Checked)
                oe.Version = "OE2003";
            else if (rdoOE2010.Checked)
                oe.Version = "OE2010";
            else if (rdoOES2003.Checked)
                oe.Version = "OE Speaker 2003";

            oe.Directory = txtOEDir.Text;
            oe.FileExt = txtOEFileExt.Text;

            pr.EventSoftwareType = oe;

            MainApp.CurrentProject = pr;
            MainApp.LastProjects.Insert(0, pr);
            MainApp.SaveRecentProjects();
        }

        private void wizardPage3_ShowFromBack(object sender, EventArgs e)
        {
            wizard1.PageIndex = 1;
        }

        private void button1_Click(object sender, EventArgs e)
        {
            OpenFileDialog ofd = new OpenFileDialog();
            ofd.Filter = "OLA5 Databases (*.h2.db)|*.h2.db";
            ofd.FileName = txtOlaDBLoc.Text;
            if (ofd.ShowDialog(this) == System.Windows.Forms.DialogResult.OK)
            {
                txtOlaDBLoc.Text = ofd.FileName;
            }
        }

        private void wizardPage5_CloseFromNext(object sender, Gui.Wizard.PageEventArgs e)
        {
            e.Page = wizardPageOLASelEvent;
        }

        private void pageOE_ShowFromBack(object sender, EventArgs e)
        {
            wizard1.PageIndex = 1;
        }

        private void wizardPage5_CloseFromBack(object sender, Gui.Wizard.PageEventArgs e)
        {
            wizard1.PageIndex = 2;
        }

        private void wizardPage3_CloseFromNext(object sender, Gui.Wizard.PageEventArgs e)
        {
            switch ((cmbOlaType.SelectedItem as OlaParser.OlaListItem).ConnectionType)
            {
                case OlaParser.OLA_CONNECTION_TYPE.OLA5_INTERNAL_DB:
                    e.Page = wizardPageOLAH2;
                    break;
                case OlaParser.OLA_CONNECTION_TYPE.OLA5_SQLSERVER:
                    e.Page = wizardPageOLASqlServer;
                    break;
            }
        }

        private void wizardPageOLASelRace_CloseFromNext(object sender, Gui.Wizard.PageEventArgs e)
        {
            BroadCastProject pr = new BroadCastProject();
            Competition c = cmbOnlineEvent.SelectedItem as Competition;
            pr.EventDate = c.Date;
            pr.EventID = c.Id;
            pr.EventName = c.Name;
            pr.Password = txtOnlinePassword.Text;

            EventSoftwareOLA ola = new EventSoftwareOLA();
            ola.ConnectionType = (cmbOlaType.SelectedItem as OlaParser.OlaListItem).ConnectionType;
            ola.EventID = (cmbOlaEvent.SelectedItem as OlaComp).Id;
            ola.RaceID = (cmbOlaRace.SelectedItem as OlaComp).Id;
            pr.EventSoftwareType = ola;
            if (ola.ConnectionType == OlaParser.OLA_CONNECTION_TYPE.OLA5_INTERNAL_DB)
            {
                ola.ConnectionSettings = txtOlaDBLoc.Text;
            }
            else
            {
                ola.ConnectionSettings = txtSQLHost.Text + ";" + txtSQLUser.Text + ";" + txtSQLPort.Text + ";" + txtSQLPassword.Text;
            }

            MainApp.CurrentProject = pr;
            MainApp.LastProjects.Insert(0, pr);
            MainApp.SaveRecentProjects();
        }

        private void wizardPageOLASelEvent_ShowFromNext(object sender, EventArgs e)
        {
            EventSoftwareOLA ola = GetOlaEventSoftware();
            IDbConnection conn = ola.GetDBConnection();
            try
            {
                conn.Open();

                IDbCommand cmd = conn.CreateCommand();
                cmbOlaEvent.Items.Clear();

                cmd.CommandText = "SELECT VersionNumber FROM Version WHERE moduleId = 1";
                try
                {
                    object res = cmd.ExecuteScalar();
                }
                catch (Exception ee)
                {
                    if (ee.Message.ToUpper().Contains("ENOUGH RIGHTS"))
                    {
                        conn.Close();
                        conn = new H2Connection("jdbc:h2://" + txtOlaDBLoc.Text.Replace(".h2.db", "") + ";AUTO_SERVER=TRUE", "root", "");
                        try
                        {
                            conn.Open();
                            cmd = conn.CreateCommand();
                            TryApplyReadRights(cmd);
                        }
                        finally
                        {
                            conn.Close();
                        }
                        conn = ola.GetDBConnection();
                        conn.Open();
                        cmd = conn.CreateCommand();
                    }
                }


                cmd.CommandText = "select eventid, name from Events";


                IDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    OlaComp cmp = new OlaComp();
                    cmp.Id = Convert.ToInt32(reader["eventid"].ToString());
                    cmp.Name = Convert.ToString(reader["name"]);
                    cmbOlaEvent.Items.Add(cmp);
                }
                reader.Close();
                cmd.Dispose();

                if (cmbOlaEvent.Items.Count > 0)
                    cmbOlaEvent.SelectedIndex = 0;
            }
            finally
            {

                conn.Close();
            }
        }

        private EventSoftwareOLA GetOlaEventSoftware()
        {
            EventSoftwareOLA ola = new EventSoftwareOLA() { ConnectionType = (cmbOlaType.SelectedItem as OlaParser.OlaListItem).ConnectionType };
            if (ola.ConnectionType == OlaParser.OLA_CONNECTION_TYPE.OLA5_INTERNAL_DB)
                ola.ConnectionSettings = txtOlaDBLoc.Text;
            else if (ola.ConnectionType == OlaParser.OLA_CONNECTION_TYPE.OLA5_SQLSERVER)
                ola.ConnectionSettings = txtSQLHost.Text + ";" + txtSQLUser.Text + ";" + txtSQLPort.Text + ";" + txtSQLPassword.Text;
            return ola;
        }

        private class OlaComp
        {
            public string Name;
            public int Id;

            public override string ToString()
            {
                return Name;
            }
        }

        private static void TryApplyReadRights(IDbCommand cmd)
        {
            cmd.CommandText = @"GRANT SELECT on Version to live;
GRANT SELECT on EVENTS to live;
GRANT SELECT ON EVENTRACES to live;
GRANT SELECT ON RESULTS to live;
GRANT SELECT ON PERSONS to live;
GRANT SELECT ON Organisations to live;
GRANT SELECT ON eventclasses to live;
GRANT SELECT ON entries to live;
GRANT SELECT ON raceclasses to live;
GRANT SELECT ON splittimes to live;
GRANT SELECT ON SplitTimeControls to live;
GRANT SELECT ON Controls to live;
GRANT SELECT ON RaceClassCourses to live;
GRANT SELECT ON Courses to live;
GRANT SELECT ON baseclasses to live;
GRANT SELECT ON raceClassSplitTimeControls to live;

";
            cmd.ExecuteNonQuery();
        }

        private void wizardPageOLASelRace_ShowFromNext(object sender, EventArgs e)
        {
            EventSoftwareOLA ola = GetOlaEventSoftware();

            /*Try Connect*/
            IDbConnection conn = null;
            try
            {
                conn = ola.GetDBConnection();
                conn.Open();

                IDbCommand cmd = conn.CreateCommand();
                cmbOlaRace.Items.Clear();
                cmd.CommandText = "select eventraceid, name from EventRaces where eventid = " + ((OlaComp)cmbOlaEvent.SelectedItem).Id;
                IDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    OlaComp cmp = new OlaComp();
                    cmp.Id = Convert.ToInt32(reader["eventraceid"].ToString());
                    cmp.Name = Convert.ToString(reader["name"]);
                    cmbOlaRace.Items.Add(cmp);
                }
                reader.Close();
                cmd.Dispose();

                if (cmbOlaRace.Items.Count > 0)
                    cmbOlaRace.SelectedIndex = 0;

            }
            catch (Exception ee)
            {
                MessageBox.Show(this, ee.Message);
            }
            finally
            {
                if (conn != null)
                    conn.Close();
            }
        }

        private void wizardPageOLASqlServer_CloseFromNext(object sender, Gui.Wizard.PageEventArgs e)
        {
            e.Page = wizardPageOLASelEvent;
        }
    }
}
